using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using Microsoft.SqlServer.Management.Common;
using System.IO;

namespace SelfCreatingStoredProcedure
{
	public class SelfCreatingStoredProcedure
	{
		
		public object[] callStoredProcedure(string procedureName,SqlParameter[] parameters)
		{
			try
			{
				SqlSmoObject[] smoObj = getScript(procedureName, "MCNEDev35", @"C:\Users\StoredProcedures.sql");

				string connect = System.Configuration.ConfigurationManager.AppSettings["conn"];
				SqlConnection scn = new SqlConnection(connect);
				string sp = ((StoredProcedure)smoObj[0]).TextBody;

				string[] sArr= sp.Split(new char[]{';'});
				sp = sArr[1];

				SqlCommand spcmd = new SqlCommand(sp, scn);
				spcmd.CommandType = CommandType.Text;

				SqlDataReader dr;

				foreach (SqlParameter s in parameters)
				{
					spcmd.Parameters.Add(s);
				}
 
				scn.Open();

				dr = spcmd.ExecuteReader();

				object[] val = null;
				while (dr.Read())
				{
					val = new object[dr.FieldCount];
					dr.GetValues(val);
				}

				scn.Close();
				dr.Close();
				return val;
			}
			catch (SqlException x)
			{
				Console.WriteLine(x.Message.ToString());
				return null;
			}
		}

		private SqlSmoObject[] getScript(string procedureName, string dbname, string filePath)
		{
			//check if file exists
			bool fileExists = File.Exists(filePath);
			
			SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["conn"]);
			ServerConnection c = new ServerConnection(sqlConnection);
			Server server = new Server(c);
			
			if (!fileExists)
			{
				List<SqlSmoObject> list = new List<SqlSmoObject>();

				Database db = server.Databases[dbname];

				DataTable dataTable = db.EnumObjects(DatabaseObjectTypes.StoredProcedure);

				var dataRows = from DataRow r in dataTable.Rows where (string)r.ItemArray[2] == procedureName select r;

				foreach (DataRow row in dataRows)
				{
					string sSchema = (string)row["Schema"];
					if (sSchema == "sys" || sSchema == "INFORMATION_SCHEMA")
						continue;
					StoredProcedure sp = (StoredProcedure)server.GetSmoObject(
					   new Urn((string)row["Urn"]));
					if (!sp.IsSystemObject)
					{
						list.Add(sp);
					}
				}

				Scripter scripter = new Scripter();
				scripter.Server = server;
				scripter.Options.IncludeHeaders = true;
				scripter.Options.SchemaQualify = true;
				scripter.Options.ToFileOnly = true;
				scripter.Options.FileName = filePath;
				scripter.Script(list.ToArray());
				return list.ToArray();
			}
			else
			{
				StoredProcedure sp = new StoredProcedure();
				sp.Parent = server.Databases[dbname];
				sp.Schema = "";
				sp.Name = procedureName;
				sp.TextBody = File.ReadAllText(filePath);

				List<SqlSmoObject> list = new List<SqlSmoObject>();
				list.Add(sp);
				return list.ToArray();
			}
		}
	}
}
